"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.
"""

"""
Case Type   : 资源池化，unlogged表清空缓存清理
Case Name   : 资源池化场景下,构造unlog表清空，检验缓存清理是否影响再插入
Create At   : 2025-12-01
Owner       : liukai
Description :
    1、查询集群状态
    2、创建unlogged表
    3、创建用户
    4、插入数据
    5、构造主机failover
    6、查询表结构是否损坏
    7、执行再插入检验是否成功
    8、清理环境
    9、恢复集群初始状态

Expect      :
    1、集群状态正常
    2、创建成功
    3、创建成功
    4、插入成功
    5、failover成功
    6、查询表结构未损坏
    7、插入成功错
    8、清理环境成功
    9、恢复集群初始状态成功
History     :
    1、2025-12-01 创建
"""
import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from testcase.utils.Logger import Logger
from yat.test import macro
from yat.test import Node

Primary_ROOTSH = CommonSH('PrimaryRoot')
Primary_SH = CommonSH('PrimaryDbUser')
Standby1_SH = CommonSH('Standby1DbUser')
Standby2_SH = CommonSH('Standby2DbUser')
@unittest.skipIf(1 == Primary_SH.get_node_num(), '单机环境不执行')
class SharedStorage(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.common = Common()
        self.s1_comsh = CommonSH('Standby1DbUser')

    def test_shared_storage(self):
        text = '-----step1:查询数据库集群状态-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)

        text = '-----step2:创建unlogged表;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop table if exists test_policy5 cascade;
                create unlogged table test_policy5(id int,usr name);
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE TABLE', status, '执行失败' + text)

        text = '-----step3:创建测试用户;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop user if exists test_user_001 cascade;
                create user test_user_001 password 'huawei@123';
                drop user if exists test_user_002 cascade;
                create user test_user_002 password 'huawei@123';
                drop user if exists test_user_003 cascade;
                create user test_user_003 password 'huawei@123';
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE ROLE', status, '执行失败' + text)

        text = '-----step4:向表中插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''
                insert into test_policy5(id, usr) values(1, 'test_user_001');
                insert into test_policy5(id, usr) values(2, 'test_user_002');
                insert into test_policy5(id, usr) values(3, 'test_user_002');
                insert into test_policy5(id, usr) values(4, 'test_user_002');
                insert into test_policy5(id, usr) values(5, 'test_user_001');
                insert into test_policy5(id, usr) values(5, 'test_user_003');
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('INSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1', status, '执行失败' + text)

        text = '-----step5:构造主机DN故障 expect:集群failover成功-----'
        self.log.info(text)
        stopdn = Primary_SH.exec_failover()
        self.log.info(stopdn)
        time.sleep(40)
        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        res1 = status.splitlines()[-1].split('|')[0].split('6001')[-1]
        res2 = status.splitlines()[-1].split('|')[1].split('6002')[-1]
        res3 = status.splitlines()[-1].split('|')[2].split('6003')[-1]
        self.log.info(res1)
        self.log.info(res2)
        self.log.info(res3)
        self.assertIn('P Down    Manually stopped', res1, '执行失败' + text)
        self.assertTrue(
                'S Primary Normal' in res2 or 'S Primary Normal' in res3,
                '执行失败: ' + text
        )
        if 'S Primary Normal' in res2:
            self.Primary_Cur = Standby1_SH
        else:
            self.Primary_Cur = Standby2_SH

        text = '-----step6:以test_user_001登录数据库，测试,查询表信息;expect:查询结果-----'
        self.log.info(text)
        exe_cmd = f'''
                    gsql -d tpcc -U test_user_001 -W huawei@123 -c "select * from test_policy5 where usr = 'test_user_001';"
                    '''
        self.log.info(exe_cmd)
        res = self.Primary_Cur.exec_cmd_under_user(exe_cmd)
        self.log.info(res)
        self.assertIn('(0 rows)', res, '执行失败' + text)

        text = '-----step7:向表中插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''
                insert into test_policy5(id, usr) values(1, 'test_user_001');
                insert into test_policy5(id, usr) values(2, 'test_user_002');
                insert into test_policy5(id, usr) values(3, 'test_user_002');
                insert into test_policy5(id, usr) values(4, 'test_user_002');
                insert into test_policy5(id, usr) values(5, 'test_user_001');
                insert into test_policy5(id, usr) values(5, 'test_user_003');
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('INSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1', status, '执行失败' + text)

        text = '-----step8:清理环境;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop table test_policy5;
                drop user if exists test_user_001 cascade;
                drop user if exists test_user_002 cascade;
                drop user if exists test_user_003 cascade;
                '''
        self.log.info(sql)
        status = self.Primary_Cur.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('DROP TABLE', status, '执行失败' + text)

    def tearDown(self):
        text = '-----step9:恢复环境为初始集群状态; expect:成功-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='stop')
        self.log.info(status)
        self.assertIn('cm_ctl: stop cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='start')
        self.log.info(status)
        self.assertIn('cm_ctl: start cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='switchover', param='-a')
        self.log.info(status)
        self.assertIn(self.constant.cm_switchover_success_msg, status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
